import os
import openpyxl
import pandas
import numpy

defultdf = pandas.DataFrame(data=[['路人甲', 20],['路人乙', 20]], columns=['姓名', '年龄'])

class Excel_FileList:
    def __init__(self):
        self.list = []
        self._get_excels()
        pass

    def _get_excels(self):
        flielist = os.listdir(".\\EXCEL素材库")
        for file in flielist:
            fname = file.split('.')[0]
            ftype = file.split('.')[1]
            if ftype == "xls" or ftype == "xlsx":
                self.list.append(f"{os.getcwd()}\\EXCEL素材库\\{fname}.{ftype}")
        pass

    def printlist(self):
        for file in self.list:
            print(file)


class Excelfile:
    def __init__(self, addr=""):
        self.faddr = addr
        self.ftype = ""
        self.fname = ""
        self.cindex = []
        self.status = False
        self.df = None
        self.resetexcel()
        if addr != "":
            self.inputexcel(addr=addr[:])
        else:
            print("新建空白EXCEL文件对象")
        pass

    def resetexcel(self):
        self.faddr = ""
        self.ftype = ""
        self.fname = ""
        self.cindex = []
        self.status = False
        self.df = None

    def _analyseaddr(self, addr):
        self.faddr = addr
        self.ftype = self.faddr.rpartition(".")[2]
        self.fname = self.faddr.rpartition("/")[2].rpartition(".")[0]
        self.status = True

    def createexcel(self, addr=os.getcwd() + "\\EXCEL表格.xlsx", df=None):
        if df is not None:
            if addr[-4:] != 'xlsx' and addr[-3:] != 'xls':
                faddr = addr[:] + '.xlsx'
            else:
                faddr = addr[:]
            self._analyseaddr(faddr)
            self.df = pandas.DataFrame(data=df)
            self.cindex = self.df.columns.tolist()
            print("新建空EXCEL表单，地址为:", faddr)

    def saveexcel(self, addr):
        wb = openpyxl.Workbook()
        ws = wb.active
        ws.append(self.df.columns.values.tolist())
        for i in self.df.values:
            ws.append(i.tolist())
        wb.save(addr)
        pass

    def movecolumn(self, cindex, newloc):
        columm = self.df.pop(cindex)
        self.df.insert(newloc, cindex, columm)

    def resetindex(self):
        self.df = self.df.reset_index(drop=True)

    def copyindex(self, excel2, cindex, cindex2copy):
        state = True
        if cindex2copy in excel2.cindex and cindex in self.cindex and excel2.cindex:
            print(excel2.df[cindex2copy])
            if cindex2copy not in self.cindex:
                self.df[cindex2copy] = pandas.Series(index=self.df.index)
                self.cindex.append(cindex2copy)
            self._copyexistindex(excel2, cindex, cindex2copy)

        else:
            state = False

        return state

    def _copyexistindex(self, excel2, cindex, cindex2copy):
        if cindex2copy in excel2.cindex and cindex in self.cindex and excel2.cindex:
            if cindex2copy in self.cindex:
                print("存在")
                for index in self.df[cindex].values.tolist():
                    if index in excel2.df[cindex].values.tolist():
                        value = excel2.df[cindex2copy][excel2.df[cindex] == index].values[0]
                        print(value)
                    else:
                        value = numpy.NAN
                        print("NaN")
                    indexlist = list(set(self.df.index * (self.df[cindex] == index)))
                    if len(indexlist) == 1:
                        self.df[cindex2copy][indexlist[0]] = value
                    else:
                        self.df[cindex2copy][indexlist[1]] = value

    def compareexcel(self, excel2, cindex):
        state = True
        df = pandas.DataFrame(columns=excel2.cindex)
        if cindex in self.cindex and cindex in excel2.cindex:
            series = self.df[cindex].values.tolist()
            for i in excel2.df.index:
                if excel2.df[cindex][i] not in series:
                    df.loc[i] = excel2.df.loc[i]
            df.dropna(how='all', axis='index')
            self.df = df.reset_index(drop=True)
        else:
            state = False
        return state

    def inputexcel(self, addr: str):
        addr.replace("/","\\")
        print(f"正在读取文件:>>>{addr}<<<")
        self._analyseaddr(addr)
        if self.ftype == "xlsx":
            self.resetexcel()
            self._analyseaddr(addr)
            self.df = self._readxlsx()
            self.cindex = self.df.columns.tolist()
            return True
        elif self.ftype == "xls":
            self.resetexcel()
            self._analyseaddr(addr)
            self.df = self._readxls()
            self.cindex = self.df.columns.tolist()
            return True
        else:
            self.status = False
            return False

    def _readxlsx(self):
        wb = openpyxl.load_workbook(self.faddr)
        sheetname = wb.sheetnames[0]
        print("工作表名称:" + sheetname)
        ws = wb[sheetname]
        n = 1
        for v in ws[1]:
            if v.value is None:
                self.cindex.append(f"未命名列{n}")
                n += 1
            else:
                self.cindex.append(v.value)
        ws.delete_rows(1)
        df = pandas.DataFrame(ws.values, columns=self.cindex)

        return df

    def _readxls(self):
        df = pandas.read_excel(self.faddr)
        self.cindex = df.columns.tolist()
        i = 0
        for n in range(len(self.cindex)):
            if self.cindex[n][0:-1] == "Unnamed: ":
                df.rename(columns={self.cindex[n]: f"未命名列{i}"}, inplace=True)
                self.cindex[n] = f"未命名列{i}"
                i += 1
            n += 1

        return df

    def clean(self):
        self.df = self.df.dropna(how='all', axis='index')


    def print(self):
        print(self.df)
        print(f"工作簿  ： {self.fname}.{self.ftype}\n")


if __name__ == "__main__":
    excellist = Excel_FileList()
    excellist.printlist()
    ef = Excelfile(excellist.list[1])
    ef.print()
    print(ef.cindex)
    pass
